{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "import pdfplumber\n",
    "import pandas as pd\n",
    "\n",
    "with pdfplumber.open('W020210414582797605311.pdf') as pdf:\n",
    "    first_page = pdf.pages[0]\n",
    "    # 获取文本，直接得到字符串，包括了换行符【与PDF上的换行位置一致，而不是实际的“段落”】\n",
    "    print(first_page.extract_text())\n",
    "    # 获取本页全部表格，也可以使用extract_table()获得单个表格\n",
    "    for table in first_page.extract_tables(): \n",
    "        #得到的table是嵌套list类型，转化成DataFrame更加方便查看和分析 \n",
    "        df = pd.DataFrame(table[1:], columns=table[0]) \n",
    "        display(df)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "行业数据下载链接：http://www.csrc.gov.cn/pub/newsite/scb/ssgshyfljg/202104/t20210414_395990.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>门类名称及代码</th>\n",
       "      <th>行业大类代码</th>\n",
       "      <th>行业大类名称</th>\n",
       "      <th>上市公司代码</th>\n",
       "      <th>上市公司简称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>农、林、牧、渔业\\n(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>000998</td>\n",
       "      <td>隆平高科</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>002041</td>\n",
       "      <td>登海种业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>002772</td>\n",
       "      <td>众兴菌业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>300087</td>\n",
       "      <td>荃银高科</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>300189</td>\n",
       "      <td>神农科技</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         门类名称及代码 行业大类代码 行业大类名称  上市公司代码 上市公司简称\n",
       "0  农、林、牧、渔业\\n(A)     01     农业  000998   隆平高科\n",
       "1           None   None   None  002041   登海种业\n",
       "2           None   None   None  002772   众兴菌业\n",
       "3           None   None   None  300087   荃银高科\n",
       "4           None   None   None  300189   神农科技"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pdfplumber\n",
    "import pandas as pd\n",
    "\n",
    "file_name = 'W020210414582797605311.pdf'\n",
    "page_num = 101\n",
    "\n",
    "df = pd.DataFrame(columns=['门类名称及代码', '行业大类代码', '行业大类名称', '上市公司代码', '上市公司简称'])\n",
    "\n",
    "with pdfplumber.open(file_name) as pdf:\n",
    "    for i_page in range(page_num):\n",
    "        first_page = pdf.pages[i_page]\n",
    "        table = first_page.extract_table()\n",
    "        # 得到的table是嵌套list类型，转化成DataFrame更加方便查看和分析 \n",
    "        df2 = pd.DataFrame(table[1:], columns=table[0]) \n",
    "        df = df.append(df2, ignore_index=True)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>门类名称及代码</th>\n",
       "      <th>行业大类代码</th>\n",
       "      <th>行业大类名称</th>\n",
       "      <th>上市公司代码</th>\n",
       "      <th>上市公司简称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>000998</td>\n",
       "      <td>隆平高科</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>002041</td>\n",
       "      <td>登海种业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>002772</td>\n",
       "      <td>众兴菌业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>300087</td>\n",
       "      <td>荃银高科</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>300189</td>\n",
       "      <td>神农科技</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>300511</td>\n",
       "      <td>雪榕生物</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>600108</td>\n",
       "      <td>亚盛集团</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>600313</td>\n",
       "      <td>农发种业</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>600354</td>\n",
       "      <td>*ST敦种</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>农、林、牧、渔业(A)</td>\n",
       "      <td>01</td>\n",
       "      <td>农业</td>\n",
       "      <td>600359</td>\n",
       "      <td>新农开发</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       门类名称及代码 行业大类代码 行业大类名称  上市公司代码 上市公司简称\n",
       "0  农、林、牧、渔业(A)     01     农业  000998   隆平高科\n",
       "1  农、林、牧、渔业(A)     01     农业  002041   登海种业\n",
       "2  农、林、牧、渔业(A)     01     农业  002772   众兴菌业\n",
       "3  农、林、牧、渔业(A)     01     农业  300087   荃银高科\n",
       "4  农、林、牧、渔业(A)     01     农业  300189   神农科技\n",
       "5  农、林、牧、渔业(A)     01     农业  300511   雪榕生物\n",
       "6  农、林、牧、渔业(A)     01     农业  600108   亚盛集团\n",
       "7  农、林、牧、渔业(A)     01     农业  600313   农发种业\n",
       "8  农、林、牧、渔业(A)     01     农业  600354  *ST敦种\n",
       "9  农、林、牧、渔业(A)     01     农业  600359   新农开发"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.fillna(method='ffill', axis=0)\n",
    "df['门类名称及代码'] = df['门类名称及代码'].map(lambda x:x.replace(\"\\n\",\"\"))\n",
    "df['行业大类名称'] = df['行业大类名称'].map(lambda x:x.replace(\"\\n\",\"\"))\n",
    "# replace_dict = {\n",
    "#     '农、林、牧、渔业\\n(A)':'农、林、牧、渔业(A)',\n",
    "#     '皮革、毛皮、羽毛及其制':'皮革、毛皮、羽毛及其制品和制鞋业',\n",
    "#     '木材加工及木、竹、藤、':'木材加工及木、竹、藤、棕、草制品业',\n",
    "#     '文教、工美、体育和娱乐':'文教、工美、体育和娱乐用品制造业',\n",
    "#     '石油加工、炼焦及核燃':'石油加工、炼焦及核燃料加工业',\n",
    "#     '化学原料及化学制品制造':'化学原料及化学制品制造业',\n",
    "#     '黑色金属冶炼及压延加工':'黑色金属冶炼及压延加工业',\n",
    "#     '有色金属冶炼及压延加工':'有色金属冶炼及压延加工业',\n",
    "#     '铁路、船舶、航空航天和':'铁路、船舶、航空航天和其它运输设备制造业',\n",
    "#     '计算机、通信和其他电子':'计算机、通信和其他电子设备制造业',\n",
    "#     '装卸搬运和其他运输代理':'装卸搬运和其他运输代理业',\n",
    "#     '电信、广播电视和卫星传':'电信、广播电视和卫星传输传输服务',\n",
    "#     '广播、电视、电影和影视':'广播、电视、电影和影视录音制作业'\n",
    "# }\n",
    "df.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine(\"mysql+pymysql://root:123456@172.17.0.3:3306/stock?charset=utf8\")\n",
    "\n",
    "df.to_sql('industry_info',engine,if_exists='replace',index_label='序号')"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "DROP DATABASE IF EXISTS `stock`;\n",
    "CREATE DATABASE IF NOT EXISTS `stock`;\n",
    "USE `stock`;\n",
    "\n",
    "DROP TABLE IF EXISTS `industry_info`;\n",
    "CREATE TABLE `industry_info` (\n",
    "\t\t\t\t\t\t\t\t\t\t\t\t`序号` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,\n",
    "                        `门类名称及代码` VARCHAR(255) NOT NULL,\n",
    "                        `行业大类代码` VARCHAR(32) NOT NULL,\n",
    "                        `行业大类名称` VARCHAR(255) NOT NULL,\n",
    "\t\t\t\t\t\t\t\t\t\t\t\t`上市公司代码` VARCHAR(255) NOT NULL UNIQUE KEY,\n",
    "\t\t\t\t\t\t\t\t\t\t\t\t`上市公司简称` VARCHAR(255) NOT NULL\n",
    ") DEFAULT CHARSET=utf8 COMMENT '所属行业信息表';"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
